<?php
require_once 'lib/controllerWithoutGetOrPost.php';
require_once 'lib/LoggedIn.php';
$loggedIn = new LoggedIn();
require_once 'view/standard-noleftbar-top.php';

//GET all the usernames in ONE query, then use the getUsername() function to return the one we want
function getAllUsers(){
    global $db;
    $users=array();
    //GET JUST THIS Funeral Home USERS + A MEMORY TREE
    $userID = $_SESSION['user'];
    $sql0 = <<< EOT
        SELECT FuneralHomeID
        FROM Users
        WHERE ID = $userID
EOT;
    $result0 = $db->query($sql0);
    $thisUser = $db->fetch($result0);
    $FHID=$thisUser['FuneralHomeID'];
    
    $users[0]=array('ID'=>0,'Username'=>'A Memory Tree','FuneralHomeID'=>0);
    $sql = <<< EOT
        SELECT ID, Username, FuneralHomeID
        FROM Users
        WHERE FuneralHomeID = $FHID
EOT;
    $result = $db->query($sql);
    if ($db->rowCount($result) > 0) {
        while($user = $db->fetch($result)){
            $thisUserID=$user['ID'];
            $users[$thisUserID]=array('ID'=>$user['ID'],'Username'=>$user['Username'],'FuneralHomeID'=>$user['FuneralHomeID']);
        }
    }
    return $users;
}

function getUsername($users,$userID){
    if(!empty($users[$userID])){
        return $users[$userID]['Username'];
    }
}
function getFuneralHome($users,$userID){
    if(!empty($users[$userID])){
        return array(
             'Username' => $users[$userID]['Username']
           , 'FuneralHomeID' => intval($users[$userID]['FuneralHomeID']) 
		);
    }else{
        return "0";
    }
}
function formatDate($dateFromDB){
    if(!(empty($dateFromDB)||$dateFromDB==0)){
	    return gmdate("d/m/Y",strtotime($dateFromDB));
	}else{
	    return '';
    }
}
function headings($sortBy, $sortOrder)
{
    if (empty($sortBy)) {
        $sortBy = 'id';
    }
    $text = '';
    $headings = array(
        'dateadded' => 'Date Added',
        'deathdate' => 'Death Date',
        'id' => 'Person ID',
        'lastname' => 'Last Name',
        'firstname' => 'First Name',
        'akaname' => 'Aka',
        'neename' => 'Nee',
        'prevname' => 'Formerly',
        'addedby' => 'Added By'
    );
    foreach ($headings as $sort => $name) {
        $sortArrow = '';
        $sortDirection = 'asc';
        if ($sortBy === $sort) {
            if ($sortOrder === 'ASC') {
                $sortArrow = '&#x25b2;'; // up pointing triangle
                $sortDirection = 'desc';
            } else {
                $sortArrow = '&#x25bc;'; // down pointing triangle
                $sortDirection = 'asc';
            }
        }
        $text .= <<< EOT
            <th style='padding:3px;font-weight:bold;white-space:nowrap'><a href='history.php?sort=$sort&amp;order=$sortDirection' style="color:#1e1e1e;">$name$sortArrow</a></th>

EOT;
    }
    return $text;
}
function showFDsubmissions($users)
{
    global $db;
	$userID = $_SESSION['user'];
    $submissions = array();
    $userName=getUsername($users,$userID);
    $thisUser=getFuneralHome($users,$userID);
	$userFHID=$thisUser['FuneralHomeID'];

    $sortBy='';
    switch($_GET['sort']){
        case 'id':
            $sortBy='Person.ID';
            break;
        case 'lastname':
            $sortBy='Person.Surname';
            break;
        case 'firstname':
            $sortBy='Person.FirstNames';
            break;
        case 'prevname':
            $sortBy='Person.FormerName';
            break;
        case 'neename':
            $sortBy='Person.NeeName';
            break;
        case 'akaname':
            $sortBy='Person.AlsoKnownAs';
            break;
        case 'deathdate':
            $sortBy='Person.DeathDate';
            break;
        case 'dateadded':

        default:
            $sortBy='Person.ID';
            break;
    }

    $sortOrder='';
    switch($_GET['order']){
        case 'desc':
            $sortOrder='DESC';
            break;
        case 'asc':
            $sortOrder='ASC';
            break;
        default:
            $sortOrder='DESC';
            break;
    }
    $sql = <<< EOT
        SELECT Person.ID, Person.Surname, Person.FirstNames, Person.FormerName, Person.NeeName, Person.AlsoKnownAs, Person.DeathDate, Person.FuneralHomeID, FuneralHomePerson.PersonID, FuneralHomePerson.UserID, FuneralHomePerson.Time, FuneralHomePerson.Charity
        FROM Person
        LEFT JOIN FuneralHomePerson
        ON Person.ID = FuneralHomePerson.PersonID
		WHERE Person.FuneralHomeID = $userFHID
        ORDER BY
         CASE WHEN $sortBy > ''
          THEN 1
          ELSE 2
         END
        , $sortBy $sortOrder
EOT;
    $result = $db->query($sql);
    if ($db->rowCount($result) > 0 && $userFHID <> 0) {
	    $rowCount=$db->rowCount($result);
        $headings = headings($_GET['sort'], $sortOrder);
	    echo <<< EOT
<ul id="forfuneraldirectors" style="width:960px;">
    <li>
    <h3 style="color:#000000;">History for $userName</h3><div><small><a href='member.php'>Back to Member Page</a></small></div><div align='right' style='position:relative;top:5px;'>$rowCount Listings</div>
	<p id="historyMessage" align='left' style='position:relative;top:5px;'>Sort history by clicking on any heading</p>
    <script type="text/javascript">
    var message = document.getElementById('historyMessage');
    message.old = message.innerHTML;
    message.innerHTML = 'Please wait, the list may take a few seconds to load. Thank you.';
    </script>
    <div name='historyContent' style='width:100%;position:relative;top:10px;'>
    <table style='border-width:1px;position:relative;left:-5px;line-height:12px;margin:5px;width:100%;'>
        <tr style='background-color:#AAAAAA;'>
            $headings
        </tr>
EOT;
        $rowNum=0;
        while($row = $db->fetch($result)){
            $person = array(
			    'ID' => intval($row['ID'])
              , 'Surname' => $row['Surname']
              , 'FirstNames' => $row['FirstNames']
              , 'FormerName' => $row['FormerName']
              , 'NeeName' => $row['NeeName']
              , 'AlsoKnownAs' => $row['AlsoKnownAs']
              , 'DeathDate' => $row['DeathDate']
              , 'FuneralHomeID' => $row['FuneralHomeID']
            );
            $submission = array(
                    'PersonID' => intval($row1['PersonID'])
                  , 'UserID' => intval($row1['UserID'])
                  , 'Time' => $row1['Time']
                  , 'Charity' => $row1['Charity']
            );
/*          TRUNCATED WHEN PREVIOUS SQL USES A LEFT JOIN INSTEAD
			$personID = $person['ID'];
		    $sql1 = <<< EOT
                SELECT PersonID, UserID, Time, Charity
                FROM FuneralHomePerson
		        WHERE PersonID = $personID
EOT;
            $result1 = $db->query($sql1);
            if ($db->rowCount($result1) > 0) {
			    $row1 = $db->fetch($result1);
                $submission = array(
                    'PersonID' => intval($row1['PersonID'])
                  , 'UserID' => intval($row1['UserID'])
                  , 'Time' => $row1['Time']
                  , 'Charity' => $row1['Charity']
                );
			}
			else{
                $submission = array(
                    'PersonID' => $personID
                  , 'UserID' => 0
                  , 'Time' => 0
                  , 'Charity' => ''
                );
			}
*/
			if($rowNum%2==1){echo "<tr style='background:#CCCCCC;'>";}else{echo "<tr>";}
            echo "
            <td style='padding:2px;'>".formatDate($submission['Time'])."</td>
            <td style='padding:2px;'>".formatDate($person['DeathDate'])."</td>
            <td style='padding:2px;'><a target='_new' href='http://www.amemorytree.co.nz/remembrance.php?personid=".$person['ID']."'>".$person['ID']."</a></td>
            <td style='padding:2px;'><a target='_new' href='http://www.amemorytree.co.nz/remembrance.php?personid=".$person['ID']."'>".$person['Surname']."</a></td>
            <td style='padding:2px;'><a target='_new' href='http://www.amemorytree.co.nz/remembrance.php?personid=".$person['ID']."'>".$person['FirstNames']."</a></td>
            <td style='padding:2px;'>".$person['AlsoKnownAs']."</td>
            <td style='padding:2px;'>".$person['NeeName']."</td>
            <td style='padding:2px;'>".$person['FormerName']."</td>
			<td style='padding:2px;white-space:nowrap'>".getUsername($users,$submission['UserID'])."</td>
        </tr>
            ";
	        $rowNum++;
        }
		echo <<< EOT
		<tr><td>&nbsp;</td></tr>
    </table>
    </div>
    </li>
</ul>
<script type="text/javascript">
var message = document.getElementById('historyMessage');
message.innerHTML = message.old;
</script>

EOT;
    }
    else{
        echo "<p><i>No deaths have been submitted online for $userName</i></p>
            <p>&nbsp;</p>";
    }
}

$loggedIn->setPermissions('FDSubmit');
if ($loggedIn->check() === FALSE) {
    echo $loggedIn->message();
} else {
    $sort = $_GET['sort'];
    showFDSubmissions(getAllUsers());
}
require_once 'view/standard-noads-bottom.php';
//$db->showAccess();
?>
